﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using DevExpress.XtraReports.UI;
using ERP_RORZE_SYSTEM;
using System.Data;

namespace ERP_RORZE_DLL.Business
{
    public partial class rpt_POStatus : DevExpress.XtraReports.UI.XtraReport
    {
        public rpt_POStatus()
        {
            InitializeComponent();
            LoadData();
        }

        public void BindData()
        {
            POID.DataBindings.Add("Text", DataSource, "POID");
            OrderNo.DataBindings.Add("Text", DataSource, "OrderNo");
            SupplierID.DataBindings.Add("Text", DataSource, "SupplierName");
            PartNo.DataBindings.Add("Text", DataSource, "PartNo");
            PartNoPurchase.DataBindings.Add("Text", DataSource, "PartNoPurchase");
            IQID.DataBindings.Add("Text", DataSource, "IQID");
            MakerID.DataBindings.Add("Text", DataSource, "Maker");
            DeliveryDate.DataBindings.Add("Text", DataSource, "DeliveryDate");
            DeliveryTerm.DataBindings.Add("Text", DataSource, "DeliveryTermName");
            POQuantity.DataBindings.Add("Text", DataSource, "POQuantity");
            Received.DataBindings.Add("Text", DataSource, "Receive");
            NGQuantity.DataBindings.Add("Text", DataSource, "NGQuantity");
            NGReturn.DataBindings.Add("Text", DataSource, "NGReturn");
            TotalRemain.DataBindings.Add("Text", DataSource, "TotalRemain");
            UnitID.DataBindings.Add("Text", DataSource, "UnitID");
            UnitIDperIQID.DataBindings.Add("Text", DataSource, "UnitIDperIQID");
            RemainByIQUnit.DataBindings.Add("Text", DataSource, "RemainByIQUnit");
            UnitPrice.DataBindings.Add("Text", DataSource, "UnitPrice");
            Currency.DataBindings.Add("Text", DataSource, "CurrencySymbol");                      
        }

        private void LoadData()
        {
            DataSet sourceDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;
            string sql = "SELECT t1.[POID], t1.[OrderNo], t7.[SupplierName], t1.[PartNo], t3.[PartNoPurchase], t3.[IQID],"
                + " t8.[EnglishName] AS Maker, CONVERT(VARCHAR(10),t1.[DeliveryDate], 103) AS DeliveryDate, t9.[DeliveryTermName],"
                + " t1.[Quantity] AS POQuantity, CASE WHEN t4.[Quantity] IS NULL THEN 0 ELSE t4.[Quantity] END AS Receive,"
                + " CASE WHEN t5.[Quantity] IS NULL THEN 0 ELSE t5.[Quantity] END AS NGQuantity,"
                + " CASE WHEN t5.[ReturnQuantity] IS NULL THEN 0 ELSE t5.[ReturnQuantity] END AS NGReturn,"
                + " (CASE WHEN t4.[Quantity] IS NULL THEN 0 ELSE t4.[Quantity] END -"
                + " CASE WHEN t5.[Quantity] IS NULL THEN 0 ELSE t5.[Quantity] END +"
                + " CASE WHEN t5.[ReturnQuantity] IS NULL THEN 0 ELSE t5.[ReturnQuantity] END) AS TotalRemain,"
                + " t3.[BuyingUnit] UnitID, t3.UnitExchange AS UnitIDperIQID,"
                + " (CASE WHEN t4.[Quantity] IS NULL THEN 0 ELSE t4.[Quantity] END -"
                + " CASE WHEN t5.[Quantity] IS NULL THEN 0 ELSE t5.[Quantity] END +"
                + " CASE WHEN t5.[ReturnQuantity] IS NULL THEN 0 ELSE t5.[ReturnQuantity] END) *"
                + " (CASE WHEN t3.UnitExchange IS NULL THEN 0 ELSE t3.UnitExchange END) AS RemainByIQUnit,"
                + " t1.[UnitPrice], t6.[CurrencySymbol]"
                + " FROM [241_PODetails] AS t1 INNER JOIN"
                + " [241_PO] AS t2 ON t1.[POID]=t2.[ID] LEFT JOIN"
                + " [222_Part] AS t3 ON t1.[PartNo]=t3.[PartNo] LEFT JOIN"
                + " [241_PartReceive] AS t4 ON t1.[OrderNo]=t4.[OrderNo] AND t1.[POID]=t4.[POID] LEFT JOIN"
                + " [241_POMistake] AS t5 ON t1.[OrderNo]=t5.[OrderNo] AND t1.[POID]=t5.[POID] LEFT JOIN"
                + " [222_Currency] AS t6 ON t1.[CurrencyID]=t6.[CurrencyID] LEFT JOIN"
                + " [222_Supplier] AS t7 ON t2.[SupplierID]=t7.[SupplierID] LEFT JOIN"
                + " [222_Maker] AS t8 ON t3.[MakerID]=t8.[MakerID] LEFT JOIN"
                + " [222_DeliveryTerm] AS t9 ON t1.[DeliveryTermID]=t9.[DeliveryTermID]"
                + " WHERE t1.[Cancelled]=0 AND t1.[Change]=0"
                + " ORDER BY t1.[POID]";
            sourceDataSet = mySQLManagement.SQL_Query(sql);
            if (sourceDataSet != null)
            {                
                this.DataSource = sourceDataSet.Tables[0];
                BindData();                
            }
        }
    }
}
